set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
insert overwrite table jms_dm.dm_ass_scan_effective_cnt_dt
select
    scan_date  --日期
     ,network_code --网点code
     ,max(network_name) as network_name --网点名称
     ,sum(scan_cnt) as scan_cnt  --操作量
     ,operate_type  --模式操作模式 1.出港 2.进港
     ,cast(sum(usage_duration)/60 as decimal(16,2)) as usage_duration--供件台使用时长
     ,cast(sum(scan_cnt)/(sum(usage_duration)/60) as decimal(16,4)) as  effective --效能
     ,scan_date as date_time
     ,scan_date as dt
from
    (
    select
        scan_date  --日期
         ,network_code --网点code
         ,max(network_name) as network_name --网点名称
         ,supply_desk_code
         ,sum(scan_cnt) as scan_cnt  --操作量
         ,operate_type  --模式操作模式 1.出港 2.进港
         ,count(1) as usage_duration
    from
        (
            select --中心，进出港类型每分钟扫描量
                scan_date,scan_hh,scan_mm,network_code,max(network_name) as network_name
                 ,count(1) as scan_cnt
                 ,operate_type
                 ,supply_desk_code
            from
                (
                    select waybill_no ,network_code,network_name
                         ,date_format(DATE_FORMAT(from_unixtime(unix_timestamp(scan_time)+10*60*60),'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as scan_date
                         ,date_format(DATE_FORMAT(from_unixtime(unix_timestamp(scan_time)+10*60*60),'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd HH') as scan_hh
                         ,date_format(DATE_FORMAT(from_unixtime(unix_timestamp(scan_time)+10*60*60),'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd HH:mm') as scan_mm
                         ,scan_time
                         ,operate_type
                         ,supply_desk_code
                    from jms_dwd.dwd_tab_ass_scan_small_upper_base_hi
                    where substr(dt,1,10) between date_add('{{ execution_date | cst_ds }}',-1) and '{{ execution_date | cst_ds }}'
                      and owned_venue_type ='335'
                      and operate_type in ('1','2')
                      and DATE_FORMAT(from_unixtime(unix_timestamp(scan_time)+10*60*60),'yyyy-MM-dd')='{{ execution_date | cst_ds }}' 
                )a  group by scan_date,scan_hh,scan_mm,network_code,operate_type,supply_desk_code
        )b group by scan_date,network_code,operate_type,supply_desk_code
)d group by scan_date,network_code,operate_type
distribute by 1;


